﻿using Models.Common;
using Models.MVC;
using System;
using System.Collections.Generic;
using System.Data;
using System.Data.SqlClient;
using System.IO;
using System.Linq;
using System.Web;
using System.Web.Mvc;
using Web.Filters;

namespace Web.Controllers.MVC
{
    [MvcAuthentication]
    public class Bill1Controller : Controller
    {
        // GET: Bill1
        public ActionResult DetailsList(int ID)
        {
            DataTable dt = FreeADO.DBUtility.DbHelperSQL.Query(@"
select BillIndex1.*,isnull(Goods.Code,'') as GoodsCode,isnull(Goods.Name,'') as GoodsName,isnull(Goods.Model,'') as GoodsModel
				,isnull(SGoods.Code,'') as SourceGoodsCode,isnull(SGoods.Name,'') as SourceGoodsName,isnull(SGoods.Model,'') as SourceGoodsModel
                ,isnull(lu.Name,'') as LingMan,isnull(tu.Name,'') as TouMan
FROM BillIndex1 left join Goods on BillIndex1.GoodsID=Goods.ID left join Goods as SGoods on BillIndex1.SourceGoodsID=SGoods.ID
        left join Users lu on BillIndex1.LingUserID=lu.ID
        left join Users tu on BillIndex1.TouUserID=tu.ID
WHERE BillIndex1.ID=" + ID).Tables[0];
            string sssss = Newtonsoft.Json.JsonConvert.SerializeObject(dt);
            Models.MVC.Bill1IndexModel m = new Models.MVC.Bill1IndexModel();
            if (dt.Rows.Count > 0)
            {
                m = Newtonsoft.Json.JsonConvert.DeserializeObject<List<Models.MVC.Bill1IndexModel>>(Newtonsoft.Json.JsonConvert.SerializeObject(dt))[0];
                m.TouDate = Convert.ToDateTime(m.TouDate).ToString("yyyy-MM-dd");
                m.LingDate = Convert.ToDateTime(m.LingDate).ToString("yyyy-MM-dd");
            }
            ViewBag.model = m;

            return View();
        }
        [HttpPost]
        public string DetailsListAsync(int IndexID)
        {





//            string s = @"
//{
//    ""msg"": """",
//    ""code"": 0,
//    ""data"": [
//        {
//		""ProName"": ""工序1"",
//		""ID"": ""1"",
//		""PID"": ""0"",
//		""InQty"": ""100"",
//		""OutQty"": ""90"",
//		""FailQty"": ""10""
//        },{
//			""ID"": ""11"",
//			""PID"": ""1"",
//			""ProName"": ""工序1_01"",
//		    ""InQty"": ""100"",
//		    ""OutQty"": ""90"",
//		    ""FailQty"": ""10"",
//            ""FailRemark"":""废品原因01""
//    		}, {
//			""ID"": ""12"",
//			""PID"": ""1"",
//			""ProName"": ""工序1_02"",
//		    ""InQty"": ""100"",
//		    ""OutQty"": ""90"",
//		    ""FailQty"": ""10"",
//            ""FailRemark"":""废品原因02""
//    		}, {
//			""ID"": ""13"",
//			""PID"": ""1"",
//			""ProName"": ""工序1_03"",
//		    ""InQty"": ""100"",
//		    ""OutQty"": ""90"",
//		    ""FailQty"": ""10"",
//            ""FailRemark"":""废品原因03""
//    		},
//        {
//		""ProName"": ""工序2"",
//		""ID"": ""2"",
//		""PID"": ""0"",
//		""InQty"": ""100"",
//		    ""OutQty"": ""90"",
//		""FailQty"": ""10""
//        },{
//			""ID"": ""21"",
//			""PID"": ""2"",
//			""ProName"": ""工序2_01"",
//		    ""InQty"": ""100"",
//		    ""OutQty"": ""90"",
//		    ""FailQty"": ""10"",
//            ""FailRemark"":""废品原因01""
//    		}, {
//			""ID"": ""22"",
//			""PID"": ""2"",
//			""ProName"": ""工序2_02"",
//		    ""InQty"": ""100"",
//		    ""OutQty"": ""90"",
//		    ""FailQty"": ""10"",
//            ""FailRemark"":""废品原因02""
//    		}, {
//			""ID"": ""23"",
//			""PID"": ""2"",
//			""ProName"": ""工序2_03"",
//		    ""InQty"": ""100"",
//		    ""OutQty"": ""90"",
//		    ""FailQty"": ""10"",
//            ""FailRemark"":""废品原因03""
//    		}
//,
//        {
//		""ProName"": ""工序3"",
//		""ID"": ""3"",
//		""PID"": ""0"",
//		""InQty"": ""100"",
//		    ""OutQty"": ""90"",
//		""FailQty"": ""10""
//        },{
//			""ID"": ""31"",
//			""PID"": ""3"",
//			""ProName"": ""工序3_01"",
//		    ""InQty"": ""100"",
//		    ""OutQty"": ""90"",
//		    ""FailQty"": ""10"",
//            ""FailRemark"":""废品原因01""
//    		}, {
//			""ID"": ""32"",
//			""PID"": ""3"",
//			""ProName"": ""工序3_02"",
//		    ""InQty"": ""100"",
//		    ""OutQty"": ""90"",
//		    ""FailQty"": ""10"",
//            ""FailRemark"":""废品原因02""
//    		}, {
//			""ID"": ""33"",
//			""PID"": ""3"",
//			""ProName"": ""工序3_03"",
//		    ""InQty"": ""100"",
//		    ""OutQty"": ""90"",
//		    ""FailQty"": ""10"",
//            ""FailRemark"":""废品原因03""
//    		}

//    ],
//    ""count"": 924,
//    ""is"": true,
//    ""tip"": ""操作成功！""
//}";
//            return s;
            GridResult result = new GridResult();
            SqlParameter[] parameters = {
                                            new SqlParameter("@IndexID",SqlDbType.Int),
            };
            parameters[0].Value = IndexID;
            DataSet ds = FreeADO.DBUtility.DbHelperSQL.RunProcedure("SP_QueryBill1BillDetailsList", parameters, "aaa", 3000);
            result.count = Convert.ToInt32(ds.Tables[0].Rows[0]["count"]);
            result.data = ds.Tables[1];
            result.totalRow = Newtonsoft.Json.JsonConvert.DeserializeObject<List<Dictionary<string, string>>>(Newtonsoft.Json.JsonConvert.SerializeObject(ds.Tables[2]))[0];
            return Newtonsoft.Json.JsonConvert.SerializeObject(result);
        }

        // GET: Bill1
        public ActionResult IndexList()
        {
            return View();
        }

        [HttpPost]
        public string IndexListAsync(int PageIndex = 1, int PageSize = 1000000000, string Search = "",int State=0)
        {
            GridResult result = new GridResult();
            SqlParameter[] parameters = {
                                            new SqlParameter("@PageIndex",SqlDbType.Int),
                                            new SqlParameter("@PageSize",SqlDbType.Int),
                                            new SqlParameter("@Search",SqlDbType.NVarChar,40000),
                                            new SqlParameter("@State",SqlDbType.Int),
            };
            parameters[0].Value = PageIndex;
            parameters[1].Value = PageSize;
            parameters[2].Value = Search;
            parameters[3].Value = State;
            DataSet ds = FreeADO.DBUtility.DbHelperSQL.RunProcedure("SP_QueryBill1IndexList", parameters, "aaa", 3000);
            result.count = Convert.ToInt32(ds.Tables[0].Rows[0]["count"]);
            result.data = ds.Tables[1];
            result.totalRow = Newtonsoft.Json.JsonConvert.DeserializeObject<List<Dictionary<string, string>>>(Newtonsoft.Json.JsonConvert.SerializeObject(ds.Tables[2]))[0];
            return Newtonsoft.Json.JsonConvert.SerializeObject(result);
        }
        public ActionResult IndexEdit(int ID = 0)
        {
            DataTable dt = FreeADO.DBUtility.DbHelperSQL.Query(@"
select BillIndex1.*,isnull(Goods.Code,'') as GoodsCode,isnull(Goods.Name,'') as GoodsName,isnull(Goods.Model,'') as GoodsModel
				,isnull(SGoods.Code,'') as SourceGoodsCode,isnull(SGoods.Name,'') as SourceGoodsName,isnull(SGoods.Model,'') as SourceGoodsModel
FROM BillIndex1 left join Goods on BillIndex1.GoodsID=Goods.ID left join Goods as SGoods on BillIndex1.SourceGoodsID=SGoods.ID
WHERE BillIndex1.ID=" + ID).Tables[0];
            string sssss = Newtonsoft.Json.JsonConvert.SerializeObject(dt);
            Models.MVC.Bill1IndexModel m = new Models.MVC.Bill1IndexModel();
            m.TouDate = DateTime.Now.ToString("yyyy-MM-dd");
            m.LingDate = DateTime.Now.ToString("yyyy-MM-dd");
            m.TouUserID = Common.CookiesManagement.GetUserModel().UserID;
            m.LingUserID = Common.CookiesManagement.GetUserModel().UserID;
            if (dt.Rows.Count > 0)
            {
                m = Newtonsoft.Json.JsonConvert.DeserializeObject<List<Models.MVC.Bill1IndexModel>>(Newtonsoft.Json.JsonConvert.SerializeObject(dt))[0];
                m.TouDate = Convert.ToDateTime(m.TouDate).ToString("yyyy-MM-dd");
                m.LingDate = Convert.ToDateTime(m.LingDate).ToString("yyyy-MM-dd");
            }
            ViewBag.model = m;

            DataTable udt = FreeADO.DBUtility.DbHelperSQL.Query("select * from Users where DepartmentID=1 and IsDeleted=0").Tables[0];
            string s = "";
            foreach (DataRow item in udt.Rows)
            {
                s = s + @"<option value=""" + item["ID"].ToString() + @""">" + item["Name"].ToString() + @"</option>";
            }
            ViewBag.Users = s;
            DataTable ddt = FreeADO.DBUtility.DbHelperSQL.Query("select * from DengJi").Tables[0];
            string ds = "";
            foreach (DataRow item in ddt.Rows)
            {
                ds =ds + @"<option value=""" + item["Name"].ToString() + @""">" + item["Name"].ToString() + @"</option>";
            }
            ViewBag.DengJi = ds;

            if (ID == 0)
            {
                DataTable mdt = FreeADO.DBUtility.DbHelperSQL.Query("select * from BillTempletIndex where BillNameID=1 and IsDeleted=0").Tables[0];
                string ms = "";
                foreach (DataRow item in mdt.Rows)
                {
                    ms = ms + @"<option value=""" + item["ID"].ToString() + @""">" + item["Name"].ToString() + @"</option>";
                }
                ViewBag.MoBan = ms;
            }
            else
            {
                ViewBag.MoBan = "";
            }

            DataTable bdt = FreeADO.DBUtility.DbHelperSQL.Query("select * from BigClass").Tables[0];
            string bs = "";
            foreach (DataRow item in bdt.Rows)
            {
                bs = bs + @"<option value=""" + item["ID"].ToString() + @""">" + item["Name"].ToString() + @"</option>";
            }
            ViewBag.BigClass = bs;


            return View();
        }
        [HttpPost]
        public ActionResult IndexEditAsync(Models.MVC.Bill1IndexModel m)
        {
            Result<string> result = Web.Common.Bill1.IndexEdit(m);
            return Json(result);
        }
        [HttpPost]
        public ActionResult IndexDelAsync(string IDs)
        {
            Result<string> result = new Result<string>();
            try
            {

                    FreeADO.DBUtility.DbHelperSQL.ExecuteSql(@"UPDATE BillIndex1 set IsDeleted=1 where ID in (" + IDs + @")");
                    result.Type = AjaxResultType.Success;
                    result.Content = "成功";
                    result.Data = "成功";

            }
            catch (Exception e)
            {

                result.Type = AjaxResultType.Error;
                result.Content = e.Message;
                result.Data = e.Message;
            }
            return Json(result);
        }
        [HttpPost]
        public ActionResult ModifyIndexStateAsync(int ID,int State)
        {
            Result<decimal> result = new Result<decimal>() { Type = AjaxResultType.Success, Content = "成功", Data = 0, TotalRows = 0 };
            try
            {
                SqlParameter[] paras ={
                    new SqlParameter("@BillNameID",SqlDbType.Int),
                    new SqlParameter("@IndexID",SqlDbType.Int),
                    new SqlParameter("@Type",SqlDbType.Int),
                    new SqlParameter("@Str",SqlDbType.NVarChar,4000),
                    new SqlParameter("@ReturnValue", SqlDbType.Int)
                                       };
                paras[0].Value = 1;
                paras[1].Value = ID;
                paras[2].Value = State;
                paras[3].Value = "";
                paras[3].Direction = ParameterDirection.InputOutput;
                paras[4].Direction = ParameterDirection.ReturnValue;

                DataSet ds = FreeADO.DBUtility.DbHelperSQL.RunProcedure("SP_ModifyIndexState", paras, "aaa", 3000);
                if (Convert.ToInt32(paras[4].Value) == 1)
                {
                    result.Type = AjaxResultType.Success;
                    result.Content = "成功";
                    result.Data = 0;
                }
                else
                {
                    result.Type = AjaxResultType.Error;
                    result.Content = paras[3].Value.ToString();
                    result.Data = 0;
                }
            }
            catch (Exception e)
            {
                result.Type = AjaxResultType.Error;
                result.Content = e.Message;
                result.Data = 0;
            }
            return Json(result);
        }

        public ActionResult BillProceduresEdit(int IndexID = 0,int ID=0)
        {
            ViewBag.IndexID = IndexID;
            ViewBag.ID = ID;
            ViewBag.ProcedureID = 0;
            ViewBag.Remark = "";
            ViewBag.ShowIndex = 0;
            ViewBag.BiaoZhunQty = 0;
            ViewBag.GongYiID = 0;
            if (Convert.ToInt32(FreeADO.DBUtility.DbHelperSQL.GetSingle("select count(*) from BillProcedures1 where IsDeleted=0 and BillIndexID=" + IndexID)) > 0)
            {
                ViewBag.ShowIndex = Convert.ToInt32(FreeADO.DBUtility.DbHelperSQL.GetSingle("select max(ShowIndex) from BillProcedures1 where IsDeleted=0 and BillIndexID=" + IndexID)) + 1;
            }

            if (ID > 0)
            {
                DataTable fdt = FreeADO.DBUtility.DbHelperSQL.Query("select * from BillProcedures1 where ID=" + ID).Tables[0];
                ViewBag.ProcedureID = fdt.Rows[0]["ProcedureID"].ToString();
                ViewBag.Remark= fdt.Rows[0]["Remark"].ToString();
                ViewBag.ShowIndex = fdt.Rows[0]["ShowIndex"].ToString();
                ViewBag.BiaoZhunQty = fdt.Rows[0]["BiaoZhunQty"].ToString();
                ViewBag.GongYiID = fdt.Rows[0]["GongYiID"].ToString();
            }
            DataTable dt = FreeADO.DBUtility.DbHelperSQL.Query("select * from Procedures where IsDeleted=0 and State=1 and BillNameID=1").Tables[0];
            string s = "";
            foreach (DataRow item in dt.Rows)
            {
                s = s + @"<option value=""" + item["ID"].ToString() + @""">" + item["Name"].ToString() + @"</option>";
            }
            ViewBag.Procedures = s;

            return View();
        }
        [HttpPost]
        public ActionResult BillProceduresEditAsync(int IndexID = 0, int ID = 0, int ProcedureID = 0, string Remark="", int ShowIndex = 0, decimal BiaoZhunQty = 0,int GongYiID=0)
        {
            Result<int> result = new Result<int>();
            if (BiaoZhunQty <= 0)
            {
                result.Type = AjaxResultType.Error;
                result.Content = "工时标准必须大于0！";
                result.Data = 0;
                return Json(result);
            }
            try
            {
                if (ID==0)
                {
                    if (Convert.ToInt32(FreeADO.DBUtility.DbHelperSQL.GetSingle("select count(*) from BillProcedures1 where BillIndexID=" + IndexID + " and ShowIndex=" + ShowIndex + " and IsDeleted=0")) > 0)
                    {
                        FreeADO.DBUtility.DbHelperSQL.ExecuteSql("UPDATE BillProcedures1 set ShowIndex=ShowIndex+1 where ShowIndex>=" + ShowIndex + " and BillIndexID=" + IndexID + " and IsDeleted=0");
                    }

                    FreeADO.DBUtility.DbHelperSQL.ExecuteSql("insert into BillProcedures1(BillIndexID,ProcedureID,Remark,ShowIndex,BiaoZhunQty,GongYiID) values (" + IndexID + "," + ProcedureID + ",@Remark," + ShowIndex + "," + BiaoZhunQty.ToString() + "," + GongYiID.ToString() + ")", new SqlParameter[1] { new SqlParameter("@Remark", Remark) });
                }
                else
                {
                    if (Convert.ToInt32(FreeADO.DBUtility.DbHelperSQL.GetSingle("select count(*) from BillProcedures1 where BillIndexID=" + IndexID + " and ShowIndex=" + ShowIndex + " and IsDeleted=0 and ID<>" + ID)) > 0)
                    {
                        FreeADO.DBUtility.DbHelperSQL.ExecuteSql("UPDATE BillProcedures1 set ShowIndex=ShowIndex+1 where ShowIndex>=" + ShowIndex + " and BillIndexID=" + IndexID + " and IsDeleted=0 and ID<>" + ID);
                    }
                    FreeADO.DBUtility.DbHelperSQL.ExecuteSql("update BillProcedures1 set ProcedureID=" + ProcedureID + ",Remark=@Remark,ShowIndex=" + ShowIndex + @",BiaoZhunQty=" + BiaoZhunQty.ToString() + ",GongYiID=" + GongYiID + " where ID=" + ID, new SqlParameter[1] { new SqlParameter("@Remark", Remark) });
                }
                result.Type = AjaxResultType.Success;
                result.Content = "成功。";
                result.Data = 0;

            }
            catch (Exception e)
            {

                result.Type = AjaxResultType.Error;
                result.Content = e.Message;
                result.Data = 0;
            }
            return Json(result);
        }

        public ActionResult BillDetailsEdit(int BillProcedureID = 0, int ID = 0)
        {
            DataTable dt = FreeADO.DBUtility.DbHelperSQL.Query("select * from BillDetails1 where ID=" + ID).Tables[0];
            Bill1DetailsModel m = new Bill1DetailsModel();
            m.BillProcedureID = BillProcedureID;
            m.GongYi = FreeADO.DBUtility.DbHelperSQL.GetSingle("select Remark from BillProcedures1 where ID=" + BillProcedureID).ToString();
            m.InQty = Convert.ToDecimal(FreeADO.DBUtility.DbHelperSQL.GetSingle("select dbo.Fn_Value_GetOutQty(1," + BillProcedureID + ")"));
            m.OutQty = m.InQty;
            m.OperatorUserID = Common.CookiesManagement.GetUserModel().UserID;
            m.GongYiID = Convert.ToInt32(FreeADO.DBUtility.DbHelperSQL.GetSingle("select GongYiID from BillProcedures1 where ID=" + BillProcedureID));
            m.ProcedureID = Convert.ToInt32(FreeADO.DBUtility.DbHelperSQL.GetSingle("select ProcedureID from BillProcedures1 where ID=" + BillProcedureID));
            if (dt.Rows.Count>0)
            {
                m = Newtonsoft.Json.JsonConvert.DeserializeObject<List<Models.MVC.Bill1DetailsModel>>(Newtonsoft.Json.JsonConvert.SerializeObject(dt))[0];
                m.CreateDate = Convert.ToDateTime(m.CreateDate).ToString("yyyy-MM-dd");
                m.BeginDate = Convert.ToDateTime(m.BeginDate) < new DateTime(2000, 01, 01) ? "" : Convert.ToDateTime(m.BeginDate).ToString("yyyy-MM-dd");
                m.EndDate = Convert.ToDateTime(m.EndDate) < new DateTime(2000, 01, 01) ? "" : Convert.ToDateTime(m.EndDate).ToString("yyyy-MM-dd");
                m.ProcedureID = Convert.ToInt32(FreeADO.DBUtility.DbHelperSQL.GetSingle("select ProcedureID from BillProcedures1 where ID=" + BillProcedureID));
            }
            ViewBag.model = m;
            DataTable udt = FreeADO.DBUtility.DbHelperSQL.Query("select * from Users where DepartmentID=1 and IsDeleted=0").Tables[0];
            string s = "";
            foreach (DataRow item in udt.Rows)
            {
                s = s + @"<option value=""" + item["ID"].ToString() + @""">" + item["Name"].ToString() + @"</option>";
            }
            ViewBag.Users = s;

            return View();
        }
        [HttpPost]
        public ActionResult BillDetailsEditAsync(Bill1DetailsModel m)
        {
            Result<int> result = Web.Common.Bill1.BillDetailsEdit(m);
            return Json(result);
        }
        [HttpPost]
        public ActionResult BillProceduresDelAsync(string IDs)
        {
            Result<string> result = new Result<string>();
            try
            {

                FreeADO.DBUtility.DbHelperSQL.ExecuteSql(@"Delete BillDetails1 where BillProcedureID in (" + IDs + @")");
                FreeADO.DBUtility.DbHelperSQL.ExecuteSql(@"Delete BillProcedures1 where ID in (" + IDs + @")");
                result.Type = AjaxResultType.Success;
                result.Content = "成功";
                result.Data = "成功";
            }
            catch (Exception e)
            {

                result.Type = AjaxResultType.Error;
                result.Content = e.Message;
                result.Data = e.Message;
            }
            return Json(result);
        }
        [HttpPost]
        public ActionResult BillDetailsDelAsync(string IDs)
        {
            Result<string> result = new Result<string>();
            try
            {

                FreeADO.DBUtility.DbHelperSQL.ExecuteSql(@"Delete BillDetails1 where ID in (" + IDs + @")");
                result.Type = AjaxResultType.Success;
                result.Content = "成功";
                result.Data = "成功";
            }
            catch (Exception e)
            {

                result.Type = AjaxResultType.Error;
                result.Content = e.Message;
                result.Data = e.Message;
            }
            return Json(result);
        }
        public ActionResult BillDetailsStuffList(int BillDetailsID)
        {
            ViewBag.ID = BillDetailsID;
            return View();
        }

        [HttpPost]
        public string BillDetailsStuffListAsync(int BillDetailsID)
        {
            GridResult result = new GridResult();
            SqlParameter[] parameters = {
                                            new SqlParameter("@BillDetailsID",SqlDbType.Int),
            };
            parameters[0].Value = BillDetailsID;
            DataSet ds = FreeADO.DBUtility.DbHelperSQL.RunProcedure("SP_QueryBill1DetailsStuffList", parameters, "aaa", 3000);
            result.count = Convert.ToInt32(ds.Tables[0].Rows[0]["count"]);
            result.data = ds.Tables[1];
            result.totalRow = Newtonsoft.Json.JsonConvert.DeserializeObject<List<Dictionary<string, string>>>(Newtonsoft.Json.JsonConvert.SerializeObject(ds.Tables[2]))[0];
            return Newtonsoft.Json.JsonConvert.SerializeObject(result);
        }
        public ActionResult BillDetailsStuffEdit(int BillDetailsID,int ID = 0)
        {
            DataTable dt = FreeADO.DBUtility.DbHelperSQL.Query(@"
select a.Qty,a.GoodsID,b.TypeID,a.ID
FROM BillDetailsStuff1 as a inner join Goods b on a.GoodsID=b.ID
WHERE a.ID=" + ID).Tables[0];
            ViewBag.BillDetailsID = BillDetailsID;
            ViewBag.ID = ID;
            ViewBag.Qty = 0;
            ViewBag.GoodsID = 0;
            ViewBag.GoodsType = 1;
            Models.MVC.Bill1IndexModel m = new Models.MVC.Bill1IndexModel();
            if (dt.Rows.Count > 0)
            {
                ViewBag.Qty = dt.Rows[0]["Qty"].ToString();
                ViewBag.GoodsID = dt.Rows[0]["GoodsID"].ToString();
                ViewBag.GoodsType = dt.Rows[0]["TypeID"].ToString();
            }
            ViewBag.model = m;




            return View();
        }
        [HttpPost]
        public ActionResult BillDetailsStuffEditAsync(int BillDetailsID,int GoodsID,int Qty,int ID)
        {
            Result<string> result = new Result<string>();
            try
            {
                if (ID == 0)
                {
                    FreeADO.DBUtility.DbHelperSQL.ExecuteSql(@"
INSERT INTO [dbo].[BillDetailsStuff1]
           ([BillDetailsID]
           ,[GoodsID]
           ,[Qty])
     VALUES
           (
'" + BillDetailsID + @"',
'" + GoodsID + @"',
'" + Qty + @"'
)
");
                    result.Type = AjaxResultType.Success;
                    result.Content = "成功";
                    result.Data = "成功";
                }
                else
                {

                    FreeADO.DBUtility.DbHelperSQL.ExecuteSql(@"
UPDATE [dbo].[BillDetailsStuff1]
   SET [BillDetailsID] = '" + BillDetailsID + @"'
      ,[GoodsID] = '" + GoodsID + @"'
      ,[Qty] = '" + Qty + @"'
 WHERE ID=" + ID + @"
");
                    result.Type = AjaxResultType.Success;
                    result.Content = "成功";
                    result.Data = "成功";
                }
            }
            catch (Exception e)
            {

                result.Type = AjaxResultType.Error;
                result.Content = e.Message;
                result.Data = e.Message;
            }
            return Json(result);
        }
        [HttpPost]
        public ActionResult BillDetailsStuffDelAsync(string IDs)
        {
            Result<string> result = new Result<string>();
            try
            {

                FreeADO.DBUtility.DbHelperSQL.ExecuteSql(@"Delete BillDetailsStuff1 where ID in (" + IDs + @")");
                result.Type = AjaxResultType.Success;
                result.Content = "成功";
                result.Data = "成功";
            }
            catch (Exception e)
            {

                result.Type = AjaxResultType.Error;
                result.Content = e.Message;
                result.Data = e.Message;
            }
            return Json(result);
        }
        public ActionResult JiaoFuList(int ID)
        {
            DataTable dt = FreeADO.DBUtility.DbHelperSQL.Query(@"
select BillIndex1.*,isnull(Goods.Code,'') as GoodsCode,isnull(Goods.Name,'') as GoodsName,isnull(Goods.Model,'') as GoodsModel
				,isnull(SGoods.Code,'') as SourceGoodsCode,isnull(SGoods.Name,'') as SourceGoodsName,isnull(SGoods.Model,'') as SourceGoodsModel
                ,isnull(lu.Name,'') as LingMan,isnull(tu.Name,'') as TouMan
FROM BillIndex1 left join Goods on BillIndex1.GoodsID=Goods.ID left join Goods as SGoods on BillIndex1.SourceGoodsID=SGoods.ID
        left join Users lu on BillIndex1.LingUserID=lu.ID
        left join Users tu on BillIndex1.TouUserID=tu.ID
WHERE BillIndex1.ID=" + ID).Tables[0];
            string sssss = Newtonsoft.Json.JsonConvert.SerializeObject(dt);
            Models.MVC.Bill1IndexModel m = new Models.MVC.Bill1IndexModel();
            if (dt.Rows.Count > 0)
            {
                m = Newtonsoft.Json.JsonConvert.DeserializeObject<List<Models.MVC.Bill1IndexModel>>(Newtonsoft.Json.JsonConvert.SerializeObject(dt))[0];
                m.TouDate = Convert.ToDateTime(m.TouDate).ToString("yyyy-MM-dd");
                m.LingDate = Convert.ToDateTime(m.LingDate).ToString("yyyy-MM-dd");
            }
            ViewBag.model = m;

            return View();
        }
        [HttpPost]
        public string JiaoFuListAsync(int IndexID)
        {

            GridResult result = new GridResult();
            
            DataTable dt = FreeADO.DBUtility.DbHelperSQL.Query(@"
select ID,CONVERT(nvarchar(50),CreateTime,120) as CreateTime,Qty,Number,Remark 
from GoodsStockRunnings where BillNameID=1 and Type=1 and BillIndexID="+IndexID+@"
order by ID desc").Tables[0];
            result.count =0;
            result.data = dt;
            result.totalRow = new Dictionary<string, string>();
            return Newtonsoft.Json.JsonConvert.SerializeObject(result);
        }
        public ActionResult JiaoFuEdit(int IndexID,int ID=0)
        {
            ViewBag.IndexID = IndexID;
            DataTable dt = FreeADO.DBUtility.DbHelperSQL.Query(@"select * from GoodsStockRunnings WHERE ID=" + ID).Tables[0];
            string sssss = Newtonsoft.Json.JsonConvert.SerializeObject(dt);
            Models.MVC.JiaoFuModel m = new Models.MVC.JiaoFuModel();
            if (dt.Rows.Count > 0)
            {
                m = Newtonsoft.Json.JsonConvert.DeserializeObject<List<Models.MVC.JiaoFuModel>>(Newtonsoft.Json.JsonConvert.SerializeObject(dt))[0];
                m.CreateTime = Convert.ToDateTime(m.CreateTime).ToString("yyyy-MM-dd");
            }
            ViewBag.model = m;

            return View();
        }
        [HttpPost]
        public ActionResult JiaoFuEditAsync(int IndexID, decimal Qty, int ID = 0, string Remark = "", string Number = "", int IsExec = 0, string CreateTime = "")
        {
            Result<decimal> result = new Result<decimal>() { Type = AjaxResultType.Success, Content = "成功", Data = 0, TotalRows = 0 };
            result = Web.Common.BillCommon.JiaoFuEditAsync(1, IndexID, Qty, ID, Remark, Number, IsExec, CreateTime);

            return Json(result);
        }
        [HttpPost]
        public ActionResult ShowIndexEditAsync(int IndexID,int BillProcedureID, int ShowIndex)
        {
            Result<decimal> result = new Result<decimal>() { Type = AjaxResultType.Success, Content = "成功", Data = 0, TotalRows = 0 };
            try
            {
                if (Convert.ToInt32(FreeADO.DBUtility.DbHelperSQL.GetSingle("select count(*) from BillProcedures1 where BillIndexID=" + IndexID + " and ShowIndex=" + ShowIndex + " and IsDeleted=0")) > 0)
                {
                    FreeADO.DBUtility.DbHelperSQL.ExecuteSql("UPDATE BillProcedures1 set ShowIndex=ShowIndex+1 where ShowIndex>=" + ShowIndex + " and BillIndexID=" + IndexID + " and IsDeleted=0");
                }

                FreeADO.DBUtility.DbHelperSQL.ExecuteSql(@"UPDATE BillProcedures1 set ShowIndex="+ShowIndex+" where ID="+BillProcedureID);
            }
            catch (Exception e)
            {
                result.Type = AjaxResultType.Error;
                result.Content = e.Message;
                result.Data = 0;
            }
            return Json(result);
        }


        public ActionResult IndexListReport1()
        {
            return View();
        }

        [HttpPost]
        public string IndexListReport1Async(int PageIndex = 1, int PageSize = 1000000000, string Search = "",string BeginDate="",string EndDate="",int State=-1)
        {
            GridResult result = new GridResult();
            SqlParameter[] parameters = {
                                            new SqlParameter("@BillNameID",SqlDbType.Int),
                                            new SqlParameter("@PageIndex",SqlDbType.Int),
                                            new SqlParameter("@PageSize",SqlDbType.Int),
                                            new SqlParameter("@Search",SqlDbType.NVarChar,40000),
                                            new SqlParameter("@BeginDate",SqlDbType.DateTime),
                                            new SqlParameter("@EndDate",SqlDbType.DateTime),
                                            new SqlParameter("@State",SqlDbType.Int),

                };
            parameters[0].Value = 1;
            parameters[1].Value = PageIndex;
            parameters[2].Value = PageSize;
            parameters[3].Value = Search;
            parameters[4].Value = BeginDate;
            parameters[5].Value = EndDate;
            parameters[6].Value = State;
            DataSet ds = FreeADO.DBUtility.DbHelperSQL.RunProcedure("SP_QueryBillIndexListReport", parameters, "aaa", 3000);
            result.count = Convert.ToInt32(ds.Tables[0].Rows[0]["count"]);
            result.data = ds.Tables[1];
            result.totalRow = Newtonsoft.Json.JsonConvert.DeserializeObject<List<Dictionary<string, string>>>(Newtonsoft.Json.JsonConvert.SerializeObject(ds.Tables[2]))[0];
            return Newtonsoft.Json.JsonConvert.SerializeObject(result);
        }
        public ActionResult IndexListReport2()
        {
            return View();
        }

        [HttpPost]
        public string IndexListReport2Async(int PageIndex = 1, int PageSize = 1000000000, string Search = "", string BeginDate = "", string EndDate = "", int State = -1)
        {
            GridResult result = new GridResult();
            SqlParameter[] parameters = {
                                            new SqlParameter("@BillNameID",SqlDbType.Int),
                                            new SqlParameter("@PageIndex",SqlDbType.Int),
                                            new SqlParameter("@PageSize",SqlDbType.Int),
                                            new SqlParameter("@Search",SqlDbType.NVarChar,40000),
                                            new SqlParameter("@BeginDate",SqlDbType.DateTime),
                                            new SqlParameter("@EndDate",SqlDbType.DateTime),
                                            new SqlParameter("@State",SqlDbType.Int),

                };
            parameters[0].Value = 1;
            parameters[1].Value = PageIndex;
            parameters[2].Value = PageSize;
            parameters[3].Value = Search;
            parameters[4].Value = BeginDate;
            parameters[5].Value = EndDate;
            parameters[6].Value = State;
            DataSet ds = FreeADO.DBUtility.DbHelperSQL.RunProcedure("SP_QueryBillFailQtyReport", parameters, "aaa", 3000);
            result.count = Convert.ToInt32(ds.Tables[0].Rows[0]["count"]);
            result.data = ds.Tables[1];
            result.totalRow = Newtonsoft.Json.JsonConvert.DeserializeObject<List<Dictionary<string, string>>>(Newtonsoft.Json.JsonConvert.SerializeObject(ds.Tables[2]))[0];
            return Newtonsoft.Json.JsonConvert.SerializeObject(result);
        }
        public ActionResult IndexListReport3()
        {
            return View();
        }

        [HttpPost]
        public string IndexListReport3Async(int PageIndex = 1, int PageSize = 1000000000, string Search = "", string BeginDate = "", string EndDate = "", int State = -1)
        {
            GridResult result = new GridResult();
            SqlParameter[] parameters = {
                                            new SqlParameter("@BillNameID",SqlDbType.Int),
                                            new SqlParameter("@PageIndex",SqlDbType.Int),
                                            new SqlParameter("@PageSize",SqlDbType.Int),
                                            new SqlParameter("@Search",SqlDbType.NVarChar,40000),
                                            new SqlParameter("@BeginDate",SqlDbType.DateTime),
                                            new SqlParameter("@EndDate",SqlDbType.DateTime),

                };
            parameters[0].Value = 1;
            parameters[1].Value = PageIndex;
            parameters[2].Value = PageSize;
            parameters[3].Value = Search;
            parameters[4].Value = BeginDate;
            parameters[5].Value = EndDate;
            DataSet ds = FreeADO.DBUtility.DbHelperSQL.RunProcedure("SP_QueryBillJiaoFuReport", parameters, "aaa", 3000);
            result.count = Convert.ToInt32(ds.Tables[0].Rows[0]["count"]);
            result.data = ds.Tables[1];
            result.totalRow = Newtonsoft.Json.JsonConvert.DeserializeObject<List<Dictionary<string, string>>>(Newtonsoft.Json.JsonConvert.SerializeObject(ds.Tables[2]))[0];
            return Newtonsoft.Json.JsonConvert.SerializeObject(result);
        }

        public ActionResult ReportUser()
        {
            DataTable udt = FreeADO.DBUtility.DbHelperSQL.Query("select * from Users where DepartmentID=1 and IsDeleted=0").Tables[0];
            string s = "";
            foreach (DataRow item in udt.Rows)
            {
                s = s + @"<option value=""" + item["ID"].ToString() + @""">" + item["Name"].ToString() + @"</option>";
            }
            ViewBag.Users = s;
            return View();
        }

        [HttpPost]
        public string ReportUserAsync(int PageIndex = 1, int PageSize = 1000000000, int UserID = 0, string BeginDate = "", string EndDate = "")
        {
            GridResult result = new GridResult();
            SqlParameter[] parameters = {
                                            new SqlParameter("@PageIndex",SqlDbType.Int),
                                            new SqlParameter("@PageSize",SqlDbType.Int),
                                            new SqlParameter("@BeginDate",SqlDbType.DateTime),
                                            new SqlParameter("@EndDate",SqlDbType.DateTime),
                                            new SqlParameter("@UserID",SqlDbType.Int),
            };
            parameters[0].Value = PageIndex;
            parameters[1].Value = PageSize;
            parameters[2].Value = BeginDate;
            parameters[3].Value = EndDate;
            parameters[4].Value = UserID;
            DataSet ds = FreeADO.DBUtility.DbHelperSQL.RunProcedure("SP_QueryBill1BillDetailsListForUser", parameters, "aaa", 3000);
            result.count = Convert.ToInt32(ds.Tables[0].Rows[0]["count"]);
            result.data = ds.Tables[1];
            result.totalRow = Newtonsoft.Json.JsonConvert.DeserializeObject<List<Dictionary<string, string>>>(Newtonsoft.Json.JsonConvert.SerializeObject(ds.Tables[2]))[0];
            return Newtonsoft.Json.JsonConvert.SerializeObject(result);
        }

        public ActionResult IndexListReport4()
        {
            return View();
        }

        public ActionResult Print(int BillIndexID)
        {
            DataTable dt = FreeADO.DBUtility.DbHelperSQL.Query(@"
select BillIndex1.*,isnull(Goods.Code,'') as GoodsCode,isnull(Goods.Name,'') as GoodsName,isnull(Goods.Model,'') as GoodsModel
				,isnull(SGoods.Code,'') as SourceGoodsCode,isnull(SGoods.Name,'') as SourceGoodsName,isnull(SGoods.Model,'') as SourceGoodsModel
                ,TouUsers.Name as TouMan,LingUsers.Name as LengMan
FROM BillIndex1 left join Goods on BillIndex1.GoodsID=Goods.ID left join Goods as SGoods on BillIndex1.SourceGoodsID=SGoods.ID
        left join Users as TouUsers on BillIndex1.TouUserID=TouUsers.ID
        left join Users as LingUsers on BillIndex1.LingUserID=LingUsers.ID
WHERE BillIndex1.ID=" + BillIndexID).Tables[0];
            string sssss = Newtonsoft.Json.JsonConvert.SerializeObject(dt);
            Models.MVC.Bill1IndexModel m = new Models.MVC.Bill1IndexModel();
            m.TouDate = DateTime.Now.ToString("yyyy-MM-dd");
            m.LingDate = DateTime.Now.ToString("yyyy-MM-dd");
            m.TouUserID = Common.CookiesManagement.GetUserModel().UserID;
            m.LingUserID = Common.CookiesManagement.GetUserModel().UserID;
            if (dt.Rows.Count > 0)
            {
                m = Newtonsoft.Json.JsonConvert.DeserializeObject<List<Models.MVC.Bill1IndexModel>>(Newtonsoft.Json.JsonConvert.SerializeObject(dt))[0];
                m.TouDate = Convert.ToDateTime(m.TouDate).ToString("yyyy-MM-dd");
                m.LingDate = Convert.ToDateTime(m.LingDate).ToString("yyyy-MM-dd");
                
            }
            ViewBag.model = m;
            SqlParameter[] parameters = {
                                            new SqlParameter("@BillNameID",SqlDbType.Int),
                                            new SqlParameter("@IndexID",SqlDbType.Int),

                };
            parameters[0].Value = 1;
            parameters[1].Value = BillIndexID;
            DataSet ds = FreeADO.DBUtility.DbHelperSQL.RunProcedure("SP_QueryBillDetailsListForPrint", parameters, "aaa", 3000);
            string s = "";
            foreach (DataRow item in ds.Tables[1].Rows)
            {
                s = s + @"
            <tr style=""height:20px;"">
                <td align=""center"">"+item["ProName"].ToString()+ @"</td>
                <td align=""center"">"+item["InQty"].ToString()+ @"</td>
                <td align=""center"">"+item["OutQty"].ToString()+ @"</td>
                <td align=""center"">" + item["FailQty"].ToString() + @"</td>
                <td align=""center"">" + item["FailRemark"].ToString() + @"</td>
                <td align=""center"">" + item["TaskTime"].ToString() + @"</td>
                <td align=""center"">" + item["GongYi"].ToString() + @"</td>
                <td align=""center"">" + item["Operator"].ToString() + @"</td>
                <td align=""center"">" + item["CreateDate"].ToString() + @"</td>
                <td align=""center"">" + item["CheckMan"].ToString() + @"</td>
                <td align=""center"">" + item["WenDu"].ToString() + @"</td>
                <td align=""center"">" + item["ShiDu"].ToString() + @"</td>
                <td align=""center"" colspan=""3"">" + item["Remark"].ToString() + @"</td>
            </tr>
";
            }
            for (int i = 0; i < 24-ds.Tables[1].Rows.Count; i++)
            {
                s = s + @"
            <tr style=""height:20px;"">
                <td align=""center""></td>
                <td align=""center""></td>
                <td align=""center""></td>
                <td align=""center""></td>
                <td align=""center""></td>
                <td align=""center""></td>
                <td align=""center""></td>
                <td align=""center""></td>
                <td align=""center""></td>
                <td align=""center""></td>
                <td align=""center""></td>
                <td align=""center""></td>
                <td align=""center"" colspan=""3""></td>
            </tr>
";
            }
            ViewBag.TableTr = s;
            return View();
        }


        public ActionResult ReportForBill1()
        {
            ViewBag.aaa = FreeADO.DBUtility.DbHelperSQL.GetSingle("SELECT	 '{ field: '''+Name+''',  title: '''+Name+'/片'' },'	FROM  Goods as g where g.TypeID=1 and g.IsDeleted=0 and g.id in(SELECT  GoodsID  FROM MainGoodsBOM group by GoodsID) for xml path('')").ToString();
            ViewBag.bbb = FreeADO.DBUtility.DbHelperSQL.GetSingle("SELECT	 '{ field: '''+Name+'Total'',  title: '''+Name+'/片'' },'	FROM  Goods as g where g.TypeID=1 and g.IsDeleted=0 and g.id in(SELECT  GoodsID  FROM MainGoodsBOM group by GoodsID) for xml path('')").ToString();
            return View();
        }

        [HttpPost]
        public string ReportForBill1Async(int PageIndex = 1, int PageSize = 1000000000)
        {
            GridResult result = new GridResult();
            SqlParameter[] parameters = {
                                            new SqlParameter("@PageIndex",SqlDbType.Int),
                                            new SqlParameter("@PageSize",SqlDbType.Int)
            };
            parameters[0].Value = PageIndex;
            parameters[1].Value = PageSize;
            DataSet ds = FreeADO.DBUtility.DbHelperSQL.RunProcedure("SP_ReportBill1List", parameters, "aaa", 3000);
            result.count = Convert.ToInt32(ds.Tables[0].Rows[0]["count"]);
            result.data = ds.Tables[1];
            result.totalRow = new Dictionary<string, string>();
            return Newtonsoft.Json.JsonConvert.SerializeObject(result);
        }
        public FileResult Down(int ID)
        {


            DataTable dt = FreeADO.DBUtility.DbHelperSQL.Query(@"
select pro.Name as ProName
		,isnull(sum(bd.InQty),0) as InQty
		,isnull(sum(bd.OutQty-bd.FanQty),0) as OutQty
		,isnull(sum(bd.FailQty),0) as FailQty
		,isnull(max(bd.FailRemark ),'') as FailRemark
		,isnull(sum(bd.TaskTime),0) as TaskTime
		,isnull(Max(bd.GongYi),'') as GongYi
		,isnull((select Name from Users where ID=isnull(max(bd.OperatorUserID),0)),'') as OperatorUserName
		,isnull(max(Convert(nvarchar(10),bd.EndDate,120)),'') as CreateDate
		,isnull((select Name from Users where ID=isnull(max(bd.CheckUserID),0)),'') as CheckUserName
		,isnull(max(bd.WenDu ),'') as WenDu
		,isnull(max(bd.ShiDu ),'') as ShiDu
		,isnull(max(bd.Remark ),'') as Remark
        ,'' as a
	from BillProcedures1 as bp inner join [Procedures] as pro on bp.ProcedureID=pro.ID inner join BillIndex1 as bi on bp.BillIndexID=bi.ID
		left join BillDetails1 as bd on bd.BillProcedureID=bp.ID
	where bp.BillIndexID="+ID+@"
	group by pro.Name,bp.GongYiID,bp.ShowIndex--bp.id,bp.ProcedureID,pro.Name,bi.State,bp.Remark,bp.ShowIndex,bp.BiaoZhunQty,bp.GongYiID
	order by bp.ShowIndex
                ").Tables[0];
            DataTable dt1 = FreeADO.DBUtility.DbHelperSQL.Query(@"
select BillIndex1.*,isnull(Goods.Code,'') as GoodsCode,isnull(Goods.Name,'') as GoodsName,isnull(Goods.Model,'') as GoodsModel
				,isnull(SGoods.Code,'') as SourceGoodsCode,isnull(SGoods.Name,'') as SourceGoodsName,isnull(SGoods.Model,'') as SourceGoodsModel
                ,isnull(lu.Name,'') as LingMan,isnull(tu.Name,'') as TouMan
FROM BillIndex1 left join Goods on BillIndex1.GoodsID=Goods.ID left join Goods as SGoods on BillIndex1.SourceGoodsID=SGoods.ID
        left join Users lu on BillIndex1.LingUserID=lu.ID
        left join Users tu on BillIndex1.TouUserID=tu.ID
WHERE BillIndex1.ID=" + ID).Tables[0];
            string sssss = Newtonsoft.Json.JsonConvert.SerializeObject(dt);
            Models.MVC.Bill1IndexModel m = new Models.MVC.Bill1IndexModel();
            if (dt.Rows.Count > 0)
            {
                m = Newtonsoft.Json.JsonConvert.DeserializeObject<List<Models.MVC.Bill1IndexModel>>(Newtonsoft.Json.JsonConvert.SerializeObject(dt1))[0];
                m.TouDate = Convert.ToDateTime(m.TouDate).ToString("yyyy-MM-dd");
                m.LingDate = Convert.ToDateTime(m.LingDate).ToString("yyyy-MM-dd");
            }
            MemoryStream ms = Common.ExcelTool.Bill1ToExcel(dt, m);
            return File(ms, "application/vnd.ms-excel", m.Code+".xls");
        }

    }
}